<?php

namespace App\Services\Statistics;

use Carbon\Carbon;
use App\Models\User;
use App\Models\Order;
use App\Models\Comment;
use App\Models\Product;
use App\Models\ProductSku;
use App\Models\GoldCharge;
use App\Models\GoldRecord;
use App\Models\OrderRefund;
use App\Models\Withdraw;
use App\Models\GoldCoin;
use App\Models\GoldBean;
use App\Models\GoldSubsidy;
use App\Models\GoldCash;
use App\Models\GoldProduct;
use App\Models\GoldSplit;
use Illuminate\Support\Facades\DB;

class SearchCount
{
	/*---------- gold charges ------------------------------------------------------------------------------------------*/
	
	/** total_gold_charges_money_now */

	public function searchGoldChargesMoney($result)
	{
		return GoldCharge::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('total_amount');
	}
	
	/*---------- withdraw ------------------------------------------------------------------------------------------*/

	public function searchWithdrawActualMoney($result)
	{
		return Withdraw::query()
			->where('status', 'paid')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('actual_amount');
	}

	public function searchWithdrawChargeMoney($result)
	{
		return Withdraw::query()
			->where('status', 'paid')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('charge_amount');
	}

	public function searchPendingWithdrawByAlipay($result)
	{
		return Withdraw::query()
			->where('status', 'pending')
			->where('method', 'alipay')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('actual_amount');
	}

	public function searchPendingWithdrawByBank($result)
	{
		return Withdraw::query()
			->where('status', 'pending')
			->where('method', 'mybank')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('actual_amount');
	}

	/*---------- gold beans --------------------------------------------------------------------------------------------*/

	public function searchGoldBeansFee($result)
	{
		$mockedUser = User::where('is_mock',1)->pluck('id')->toArray();
		$mockUserId = !empty($mockedUser) ? $mockedUser : []; 

		$query = GoldRecord::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->whereNotIn('user_id', $mockUserId);
		if($result['gold_round_id']){
			$query = $query->where('gold_round_id', $result['gold_round_id'])->sum('pay_gold_beans');
		}else{

			$query = $query->sum('pay_gold_beans');
		}
		return $query;	
	}


	public function searchGoldBeansFeeTotal($result)
	{
		$mockedUser = User::where('is_mock',1)->pluck('id')->toArray();
		$mockUserId = !empty($mockedUser) ? $mockedUser : []; 
		$query = GoldRecord::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->whereNotIn('user_id', $mockUserId)
			->sum('pay_gold_beans');
		
		return $query;	
	}
	/*---------- user --------------------------------------------------------------------------------------------*/

	public function searchUserGoldCoins()
	{
		return User::query()
			->sum('gold_coins');
	}

	public function searchUserSupportGoldCoins()
	{
		return GoldCoin::query()
			->where('remark','support_gold_coins')
			->sum('money');
	}


	public function searchUserGoldBeans()
	{
		return User::query()
			->sum('gold_beans');
	}

	public function searchUserSupportGoldBeans()
	{
		return GoldBean::query()
			->where('remark','support_gold_beans')
			->sum('money');
	}

	public function searchUserTotalAmount()
	{
		return User::query()
			->count();
	}

	public function searchUserGoldLevelGreaterThanZeroTotalAmount()
	{
		return User::query()
			->where('is_gold_master' , 1)
			->count();
	}

	public function searchUserGrowth($result)
	{
		return User::query()
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->count();
	}

	/*---------- Order --------------------------------------------------------------------------------------------*/

	public function searchProductPriceIsDelivered($result)
	{
		return Order::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->where(function ($query) {
   				 $query->orWhere('delivery_status', 'delivered');
   				 $query->orWhere('delivery_status', 'received');
  			 })
			->sum('total_amount');
	}

	public function searchProductPriceTotal($result)
	{
		return Order::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('total_amount');
	}

	public function searchGoldProductPriceIsDelivered($result)
	{
		return Order::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->where(function ($query) {
   				 $query->orWhere('delivery_status', 'delivered');
   				 $query->orWhere('delivery_status', 'received');
  			 })
			->where(function ($query) {
   				 $query->orWhere('is_gold_bean', true);
   				 $query->orWhere('is_gold_coin', true);
  			 })
			->sum('total_amount');
	}

	public function searchGoldProductPrice($result)
	{
		return Order::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->where(function ($query) {
   				 $query->orWhere('is_gold_bean', true);
   				 $query->orWhere('is_gold_coin', true);
  			 })
			->sum('total_amount');
	}

	public function searchProductCostIsDelivered($result)
	{
		return Order::query()
			->join('order_items','orders.id','=','order_items.order_id')
			->whereNotNull('orders.paid_at')
			->where('orders.created_at', '>', $result['start'])
			->where('orders.created_at', '<', $result['end'])
			->where(function ($query) {
   				 $query->orWhere('orders.delivery_status', 'delivered');
   				 $query->orWhere('orders.delivery_status', 'received');
  			 })
			->sum('order_items.total_cost');
	}

	public function searchProductCost($result)
	{
		return Order::query()
			->join('order_items','orders.id','=','order_items.order_id')
			->whereNotNull('orders.paid_at')
			->where('orders.created_at', '>', $result['start'])
			->where('orders.created_at', '<', $result['end'])
			->sum('order_items.total_cost');
	}


	/*---------- gold records ------------------------------------------------------------------------------------------*/

	public function searchGoldRecordsCount($result)
	{
		$mockedUser = User::where('is_mock',1)->pluck('id')->toArray();
		$mockUserId = !empty($mockedUser) ? $mockedUser : []; 
		$query = GoldRecord::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->whereNotIn('user_id', $mockUserId);

		if($result['gold_round_id']){
			$query = $query->where('gold_round_id', $result['gold_round_id'])->count();
		}else{
			$query = $query->count();
		}
		return $query;
	}

	public function searchGoldRecordsMoney($result)
	{
		$mockedUser = User::where('is_mock',1)->pluck('id')->toArray();
		$mockUserId = !empty($mockedUser) ? $mockedUser : []; 
		$query = GoldRecord::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->whereNotIn('user_id', $mockUserId);

		if($result['gold_round_id']){
			$query = $query->where('gold_round_id', $result['gold_round_id'])->sum('pay_net_price');
		}else{
			$query = $query->sum('pay_net_price');
		}

		return $query;
	}

	public function searchGoldRecordsMoneyRange($result)
	{
		$mockedUser = User::where('is_mock',1)->pluck('id')->toArray();
		$mockUserId = !empty($mockedUser) ? $mockedUser : []; 
		$mockUserId = implode($mockUserId,',');

		if($result['gold_round_id']){
			if($mockUserId){ 
				$sql = "select count(*) as count from (select sum(pay_net_price) as total from gold_records where created_at > '{$result['start']}' and created_at < '{$result['end']}' and user_id not in ({$mockUserId}) and gold_round_id = {$result['gold_round_id']} GROUP BY user_id) a where total > {$result['min']} and total <= {$result['max']}";
			}else{
				$sql = "select count(*) as count from (select sum(pay_net_price) as total from gold_records where created_at > '{$result['start']}' and created_at < '{$result['end']}'  and gold_round_id = {$result['gold_round_id']} GROUP BY user_id) a where total > {$result['min']} and total <= {$result['max']}";
			}
			
		}else{
			if($mockUserId){
				$sql = "select count(*) as count from (select sum(pay_net_price) as total from gold_records where created_at > '{$result['start']}' and created_at < '{$result['end']}' and user_id not in ({$mockUserId}) GROUP BY user_id) a where total > {$result['min']} and total <= {$result['max']}";
			}else{
				$sql = "select count(*) as count from (select sum(pay_net_price) as total from gold_records where created_at > '{$result['start']}' and created_at < '{$result['end']}'  GROUP BY user_id) a where total > {$result['min']} and total <= {$result['max']}";

			}	
			
		}
		
		$query = DB::select($sql, [1]);
		return $query[0]->count;
	}

	public  function searchGoldRecordsUserCount($result)
	{
		$mockedUser = User::where('is_mock',1)->pluck('id')->toArray();
		$mockUserId = !empty($mockedUser) ? $mockedUser : []; 
		
		$query = GoldRecord::query()
			->whereNotNull('paid_at')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->whereNotIn('user_id', $mockUserId);

		if($result['gold_round_id']){
			$query = $query->where('gold_round_id', $result['gold_round_id'])->distinct('user_id')->count();
		}else{
			$query = $query->distinct('user_id')->count();
		}
		return $query;
	}

	public  function searchUserCandidateTotalAmount()
	{
		$mockedUser = User::where('is_mock',1)->pluck('id')->toArray();
		$mockUserId = !empty($mockedUser) ? $mockedUser : []; 
		$count = GoldRecord::query()
			->whereNotNull('paid_at')
			->whereNotIn('user_id', $mockUserId)
			->distinct('user_id')
			->count();

		return $count;
	}

	/*---------- gold coins ------------------------------------------------------------------------------------------*/

	public function searchGoldCoinsDirectPrize($result)
	{
		return GoldCoin::query()
			->where('remark', 'gold_record_direct_prize')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}

	public function searchGoldCoinsTeamPrize($result)
	{
		return GoldCoin::query()
			->where('remark', 'gold_record_team_prize')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}

	public function searchGoldCoinsOverTime($result)
	{
		return GoldBean::query()
			->where('remark', 'overtime_fine_beans')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}

	public function searchGoldBeansPresent($result)
	{
		return GoldBean::query()
			->where('remark', 'order_gold_beans')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}

	/*---------- gold subsidies ------------------------------------------------------------------------------------------*/

	public function searchGoldSubsidiesUnlocked($result)
	{
		return GoldSubsidy::query()
			->where('remark', 'unlocked_gold_subsidies')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}
	
	/*---------- gold cashes ------------------------------------------------------------------------------------------*/
	public function searchGoldCashesBuy($result)
	{
		return GoldBean::query()
			->where('remark', 'buying_gold_cashes')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}

	public function searchGoldCashesFromBeans($result)
	{
		return GoldCash::query()
			->where('remark', 'gold_cashes_from_beans')
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->sum('money');
	}

	public function searchGroundingProduct($result)
	{
		return GoldProduct::query()
			->where('started_at', '>', $result['start'])
			->where('started_at', '<', $result['end'])
			->count();
	}

	public function searchPendingSplit($result)
	{
		return GoldRecord::query()
			->where('created_at', '>', $result['start'])
			->where('created_at', '<', $result['end'])
			->whereNotNull('to_split_applied_at')
			->count();
	}
}
